﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_MoneyRequestTravelers : IDAL_FIM_MoneyRequestTravelers
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public DataTable GetFull(Nullable<int> money_req_seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "SELECT tv.traveler_seq, tv.money_req_seq, tv.emp_seq"
                    + ", tv.contact_seq, tv.job_site_seq, tv.pos_seq, tv.class_seq, tv.old_class_seq"

                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", NameTh as contact_name"

                    + ", CASE WHEN tv.emp_seq is not null THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " ELSE NameTh"
                        + " END AS traveler_name"

                    + ", CASE WHEN tv.emp_seq is not null THEN pos.pos_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS pos_name"

                    + ", CASE WHEN tv.emp_seq is not null THEN j.job_site_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS job_site_name"

                    + ", cl.class_name, old.class_name as old_class_name"

                    + " FROM fim_money_request_travelers tv"
                    + " LEFT JOIN IcmMasContact c ON tv.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON tv.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions pos ON tv.pos_seq = pos.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON tv.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_mp_classes cl ON tv.class_seq = cl.class_seq"
                    + " LEFT JOIN hrm_mp_classes old ON tv.old_class_seq = old.class_seq"
                    + " WHERE tv.record_status not like @delete_status"
                    + (money_req_seq != null ? " AND tv.money_req_seq = @money_req_seq" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable GetList(Nullable<int> money_req_seq)
        {
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);

                strSQL = "SELECT tv.traveler_seq, tv.emp_seq"

                    + ", CASE WHEN tv.emp_seq is not null THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " ELSE NameTh"
                        + " END AS traveler_name"

                    + " FROM fim_money_request_travelers tv"
                    + " LEFT JOIN IcmMasContact c ON tv.contact_seq = c.ConId"
                    //+ " LEFT JOIN fim_mas_contact_persons cp ON tv.contact_person_seq = cp.contact_person_seq"
                    + " LEFT JOIN hrm_ps_employees emp ON tv.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " WHERE tv.record_status not like @delete_status"
                    + (money_req_seq != null ? " AND tv.money_req_seq = @money_req_seq" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataRow GetInfo(int seq)
        {
            string strSQL;
            DataRow dr = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT tv.traveler_seq, tv.money_req_seq, tv.emp_seq"
                    + ", tv.contact_seq, tv.job_site_seq, tv.pos_seq, tv.class_seq, tv.old_class_seq"

                    + ", concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name) as emp_name"
                    + ", NameTh as contact_name"

                    + ", CASE WHEN tv.emp_seq is not null THEN concat(empnp.prefix_name, emp.first_name, ' ', emp.last_name)"
                        + " ELSE NameTh"
                        + " END AS traveler_name"

                    + ", CASE WHEN tv.emp_seq is not null THEN pos.pos_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS pos_name"

                    + ", CASE WHEN tv.emp_seq is not null THEN j.job_site_name"
                        + " ELSE 'ไม่ระบุ'"
                        + " END AS job_site_name"

                    + ", cl.class_name, old.class_name as old_class_name"

                    + " FROM fim_money_request_travelers tv"
                    + " LEFT JOIN IcmMasContact c ON tv.contact_seq = c.ConId"
                    + " LEFT JOIN hrm_ps_employees emp ON tv.emp_seq = emp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes empnp ON emp.name_prefix_seq = empnp.name_prefix_seq"
                    + " LEFT JOIN hrm_mp_positions pos ON tv.pos_seq = pos.pos_seq"
                    + " LEFT JOIN hrm_mp_job_sites j ON tv.job_site_seq = j.job_site_seq"
                    + " LEFT JOIN hrm_mp_classes cl ON tv.class_seq = cl.class_seq"
                    + " LEFT JOIN hrm_mp_classes old ON tv.old_class_seq = old.class_seq"
                    + " WHERE tv.traveler_seq = @seq";

                dr = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dr;
        }

        public void Insert(Nullable<int> money_req_seq
            , Nullable<int> emp_seq
            , Nullable<int> contact_seq
            , Nullable<int> contact_person_seq
            , Nullable<int> job_site_seq
            , Nullable<int> pos_seq
            , Nullable<int> class_seq
            , Nullable<int> old_class_seq
            , string record_status
            , Nullable<int> created_by
            , Nullable<DateTime> created_when)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@contact_seq", contact_seq);
                dbManager.AddParameters(3, "@contact_person_seq", "");
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@pos_seq", pos_seq);
                dbManager.AddParameters(6, "@class_seq", class_seq);
                dbManager.AddParameters(7, "@old_class_seq", old_class_seq);

                dbManager.AddParameters(8, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(9, "@created_by", created_by);
                dbManager.AddParameters(10, "@created_when", created_when);

                strSQL = "INSERT INTO fim_money_request_travelers"
                    + "(money_req_seq"
                        + ", emp_seq"
                        + ", contact_seq"
                        //+ ", contact_person_seq"
                        + ", job_site_seq"
                        + ", pos_seq"
                        + ", class_seq"
                        + ", old_class_seq"

                        + ", record_status"
                        + ", created_by"
                        + ", created_when)"
                    + " VALUES (@money_req_seq"
                        + ", @emp_seq"
                        + ", @contact_seq"
                        //+ ", @contact_person_seq"
                        + ", @job_site_seq"
                        + ", @pos_seq"
                        + ", @class_seq"
                        + ", @old_class_seq"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int traveler_seq
            , Nullable<int> money_req_seq
            , Nullable<int> emp_seq
            , Nullable<int> contact_seq
            , Nullable<int> contact_person_seq
            , Nullable<int> job_site_seq
            , Nullable<int> pos_seq
            , Nullable<int> class_seq
            , Nullable<int> old_class_seq
            , Nullable<int> updated_by
            , Nullable<DateTime> updated_when)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@traveler_seq", traveler_seq);
                dbManager.AddParameters(1, "@money_req_seq", money_req_seq);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@contact_seq", contact_seq);
                dbManager.AddParameters(4, "@contact_person_seq", "");
                dbManager.AddParameters(5, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(6, "@pos_seq", pos_seq);
                dbManager.AddParameters(7, "@class_seq", class_seq);
                dbManager.AddParameters(8, "@old_class_seq", old_class_seq);

                dbManager.AddParameters(9, "@updated_by", updated_by);
                dbManager.AddParameters(10, "@updated_when", updated_when);

                strSQL = "UPDATE fim_money_request_travelers"
                    + " SET money_req_seq = @money_req_seq"
                    + ", emp_seq = @emp_seq"
                    + ", contact_seq = @contact_seq"
                    //+ ", contact_person_seq = @contact_person_seq"
                    + ", job_site_seq = @job_site_seq"
                    + ", pos_seq = @pos_seq"
                    + ", class_seq = @class_seq"
                    + ", old_class_seq = @old_class_seq"
                    
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE traveler_seq = @traveler_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int traveler_seq
            , Nullable<int> updated_by
            , Nullable<DateTime> updated_when)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@traveler_seq", traveler_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", updated_when);

                strSQL = "UPDATE fim_money_request_travelers"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE traveler_seq = @traveler_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
